package com.wanmait.movies.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Timestamp;
import java.util.ArrayList;

import com.wanmait.movies.util.DBHelper;
import com.wanmait.movies.vo.Discuss;
import com.wanmait.movies.vo.Movie;
import com.wanmait.movies.vo.Report;
import com.wanmait.movies.vo.User;


import com.wanmait.movies.util.DBHelper;


public class ReportDao {
	private ReportDao() {}
	private static ReportDao reportDao = new ReportDao();
	public static ReportDao getObj() {
		return reportDao;
	}

	//删除
	
			public void deleteReport(String ids) {
				Connection co = DBHelper.getDBHelper().getconn();
				PreparedStatement ps = null;
				ResultSet rs = null;
				
				String sql ="delete from report where movie_id in("+ids+");";
				try {
					
					ps=co.prepareStatement(sql);
					ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
				DBHelper.getDBHelper().close(rs, ps, co);
				}
			}
	
	
	
	// 通过电影名字查询举报列表

	public ArrayList<Report> getReportList(String m,int manegerId) {

		ArrayList<Report> list = new ArrayList<Report>();

		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "select * from report,discuss,movie,user where user.id=report.user_id and report.discuss_id=discuss.id and discuss.movie_id = movie.id and report.maneger_id=? and movie.movie_name like '%" + m + "%'";
		try {
			ps = co.prepareStatement(sql);
			ps.setInt(1, manegerId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Report r= new Report();
				Discuss d =new Discuss(); 
				User u =new User();
				Movie mo =new Movie();
				mo.setId(rs.getInt("movie.id"));
				mo.setMovieName(rs.getString("movie_name"));
				d.setMovieId(mo);
				
				d.setMovieDiscuss(rs.getString("movie_discuss"));
				u.setUserName(rs.getString("user.user_name"));
				
				d.setCheckBit(rs.getBoolean("check_bit"));
				r.setDealTime(rs.getTimestamp("deal_time"));
				r.setDiscussId(d);
				r.setUserId(u);
				list.add(r);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		
		return list;
	}
	
	//获得举报记录
	public ArrayList<Report> getReportHistory(int id,int thispage){
        ArrayList<Report> lists = new ArrayList<Report>();
		
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from report,movie,discuss,user where discuss_id=discuss.id and user.id=report.user_id and discuss.movie_id=movie.id and report.maneger_id=? limit ?,6 ";
		
		try {
			ps=co.prepareStatement(sql);
			
			ps.setInt(1, id);
			ps.setInt(2, (thispage-1)*6);
			rs=ps.executeQuery();
			while(rs.next()) {
				Report r =new Report();
				
				Discuss d= new Discuss();
				User u =new User();
				Movie m =new Movie();
				m.setId(rs.getInt("movie.id"));
				m.setMovieName(rs.getString("movie_name"));
				
				d.setMovieId(m);
				d.setMovieDiscuss(rs.getString("movie_discuss"));
				u.setUserName(rs.getString("user_name"));
				d.setCheckBit(rs.getBoolean("check_bit"));
				r.setDealTime(rs.getTimestamp("deal_time"));
				r.setDiscussId(d);
				r.setUserId(u);

				lists.add(r);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return lists;
	}
	
	//获得未审核举报
	public ArrayList<Report> getReport() {
		ArrayList<Report> lists = new ArrayList<Report>();
		
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append("  WITH c AS (SELECT discuss.id,user_name FROM discuss,user WHERE    ");
		sb.append("  user.id = discuss.user_id)   ");
		sb.append("  select * from report,user,discuss , c,movie  ");
		sb.append("  WHERE report.maneger_id=0 and report.user_id=user.id AND  ");
		sb.append("  report.discuss_id = discuss.id AND discuss.id=c.id and discuss.movie_id=movie.id ");
		
		String sql =sb.toString();
		try {
			ps=co.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next()) {
				Report r =new Report();
				User u =new User();//举报的人
				Discuss s =new Discuss();
				User us = new User();//写评论的人
				Movie m =new Movie();
				m.setMovieName(rs.getString("movie_name"));
				u.setUserName(rs.getString("user_name"));
				s.setMovieDiscuss(rs.getString("movie_discuss"));
				us.setUserName(rs.getString("c.user_name"));
				s.setUserId(us);
				s.setMovieId(m);
				
				r.setId(rs.getInt("report.id"));
				r.setUserId(u);
				r.setDiscussId(s);
				
				lists.add(r);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return lists;
	}
	
	//同意审核举报
		public void agreeReport( int id ,int manegerId) {
			
			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			Timestamp ts =new Timestamp(System.currentTimeMillis());

			String sql ="update report set maneger_id=? ,deal_time=? where id=?  ";
			try {
				
				ps=co.prepareStatement(sql);
				ps.setInt(1, manegerId);
				ps.setTimestamp(2, ts);
				ps.setInt(3, id);
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			
		}
		
		//获得未审核的数量
       public int getReportCount( ) {
			
			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			String sql ="select count(id) as c from report where maneger_id=0 ";
			try {
				ps=co.prepareStatement(sql);
				
				rs=ps.executeQuery();
				rs.next();
				
				int a =rs.getInt("c");
				
				return a;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			return 0;
		}
       public int getReportRecordsCount( ) {//获得审核的数量
			
			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			String sql ="select count(id) as c from report where maneger_id=1 ";
			try {
				ps=co.prepareStatement(sql);
				
				rs=ps.executeQuery();
				rs.next();
				
				int a =rs.getInt("c");
				
				return a;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			return 0;
		}

	//���û��ٱ���������ӵ����ݿ�
	public void getjubao(int userId ,int discussId) {
		Connection conn = DBHelper.getDBHelper().getconn();
		String sql="insert into Report(user_id,discuss_id) values(?,?)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId );
			ps.setInt(2,discussId );
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
	}

}
